<?php class ThongKesController extends AppController {
    public $uses = array();
    public $helpers = array('Paginator');
    public function index() {

    }
	
    public function sanpham($NGAY = null, $NGAYKT=null) {
		$sqla = 'select * from sanpham where created >= ' . '"' . $NGAY . '"' . ' and created <= ' . '"' . $NGAYKT . '"';
        $result = $this->ThongKe->query($sqla);
        $this->set('kqs', $result);
        $this->set('NGAY', $NGAY);
		$this->set('NGAYKT', $NGAYKT);
    }
	
	public function thongkesp(){}
    
    public function doanhthu($NGAY = null, $NGAYKT = null) {
		$sql = 'select SUM(TIENDATRA) as DoanhThu from hoadonban where NGAYBAN >= ' . '"' . $NGAY . '"' . ' and NGAYBAN <= ' . '"' . $NGAYKT . '"';
        $result = $this->ThongKe->query($sql);
        $this->set('kqs', $result);
		$this->set('NGAY',$NGAY);
		$this->set('NGAYKT', $NGAYKT);
    }
    
    public function baocaodoanhthu() {}
	
    
    function ketquakinhdoanh_form() {
        
    }



    function ketquakinhdoanh($NGAY=null, $NGAYKT=null)
    {
        //$this->layout = 'thongke';
        $sql_tongchi = 'select  SUM(SOTIEN) as tongchi from phieuchi where NGAYCHI >= ' . '"' . $NGAY . '"' . ' and NGAYCHI <= ' . '"' . $NGAYKT . '"';
        $this->set('sql', $sql_tongchi);
        $result_tongchi = $this->ThongKe->query($sql_tongchi);
        $this->set('tongchi', $result_tongchi);

        $sql_tongthuno = 'select  SUM(sotienthanhtoan) as tongthuno from thanhtoan where NGAYTHANHTOAN >= ' . '"' . $NGAY . '"' . ' and NGAYTHANHTOAN <= ' . '"' . $NGAYKT . '"';
        $result_tongthuno = $this->ThongKe->query($sql_tongthuno);
        $this->set('tongthuno', $result_tongthuno);

        $sql_tongban = 'select  SUM(tiendatra) as tongban from hoadonban where NGAYBAN >= ' . '"' . $NGAY . '"' . ' and NGAYBAN <= ' . '"' . $NGAYKT . '"';
        $result_tongban = $this->ThongKe->query($sql_tongban);
        $this->set('tongban', $result_tongban);


        $sql_tongnhap = 'select  SUM(SLNHAP * DONGIANHAP) as ' . '"tongnhap"' . ' from hoadonnhap a join chitietnhap b on a.id=b.MAHDN where NGAYNHAP >= ' . '"' . $NGAY . '"' . ' and NGAYNHAP <= ' . '"' . $NGAYKT . '"';
        $result_tongnhap = $this->ThongKe->query($sql_tongnhap);
        $this->set('tongnhap', $result_tongnhap);
		
		$this->set('NGAY',$NGAY);
		$this->set('NGAYKT',$NGAYKT);

    }

    function kqkdnhanvien_form() {
        
    }

    function kqkdnhanvien($NGAY=null,$NGAYKT=null){
        //$this->layout = 'thongke';

        $sql_tknv = 'select a.manv, b.tennv, coalesce(sum(a.tiendatra), 0) as tienbanhang, coalesce(sum(sotien), 0) as tienchi,
                                    coalesce(sum(sotienthanhtoan), 0) as tienthuno, coalesce(sum(f.slnhap*f.slnhap), 0) as tiennhap
									from hoadonban a right join nhanvien b on a.manv = b.id
									 left join phieuchi c on b.id = c.manv
									 left join thanhtoan d on b.id = d.manv
									 left join hoadonnhap e on b.id = e.manv
									 left join chitietnhap f on e.id = f.mahdn
                    where (a.NGAYBAN >= ' . '"' . $NGAY  . '")
					and (a.NGAYBAN <= ' . '"' . $NGAYKT  . '")
                group by b.id';
		
        $result_tknv = $this->ThongKe->query($sql_tknv);
        $this->set('tknvs', $result_tknv);
		$this->set('NGAY',$NGAY);
		$this->set('NGAYKT',$NGAYKT);
    }
    /*
     *
     */

    /*chamcong*/function chamcongs ($NGAY = null, $NGAYKT=null){
		$sql = 'select b.id , b.TENNV, a.NGAYLAM, a.TGBD, a.TGKT, round((a.TGKT-a.TGBD)/10000,0) as TG from chamcong a join nhanvien b on a.MANV=b.id where a.NGAYLAM >= ' . '"' . $NGAY . '"' . ' and a.NGAYLAM <= ' . '"' . $NGAYKT . '"';
        $result = $this->ThongKe->query($sql);
        $this->set('kqs', $result);
        $this->set('NGAY', $NGAY);
		$this->set('NGAYKT', $NGAYKT);
    }

    /*bangluongfunction view4(){
        $sql = 'select a.MANV, count(a.MACLV) as SOCA, (count(a.MACLV)*b.LUONG) as TIENLUONG from chamcong a, calamviec b group by a.MANV';

        $result = $this->ThongKe->query($sql);
        $this->set('kqs', $result);
    }*/

    function tonkhos($NGAY = null, $NGAYKT = null){
        //$this->layout = 'thongke';
        //$this->set('title_for_layout', 'Tồn kho');
        $sql1 = 'create or replace view tongnhap as
                (
                select b.id, d.NgayNhap, COALESCE(sum(distinct a.SLNhap), 0)  as "sl"
                from  hoadonnhap d right join chitietnhap a on d.id = a.mahdn
                    right join sanpham b on a.MASP = b.id
                    where (d.NgayNhap is null ) or (d.NgayNhap >= ' . '"' . $NGAY  . '")
					and (d.NgayNhap is null ) or (d.NgayNhap <= ' . '"' . $NGAYKT  . '")
                group by b.id
                )';
        $sql2 = 'create or replace view tongban as
                (
                select b.id, d.NgayBan, COALESCE(sum(distinct a.SLBan), 0)  as "sl"
                from  hoadonban d right join chitietban a on d.id = a.mahdb
                    right join sanpham b on a.MASP = b.id
                    where (d.NgayBan is null ) or (d.NgayBan >= ' . '"' . $NGAY  . '")
					and (d.NgayBan is null ) or (d.NgayBan <= ' . '"' . $NGAYKT  . '")
                group by b.id
                )';
        $sql3 = 'select b.id, b.tensp, COALESCE(a.sl, 0) - COALESCE(c.sl,0) as "TonKho"
                from tongnhap a right join sanpham b on a.id = b.id
                     left join tongban c on b.id = c.id
                group by b.id';
        $result1 = $this->ThongKe->query($sql1);
        $this->set('kq1s', $result1);
        $result2 = $this->ThongKe->query($sql2);
        $this->set('kq2s', $result2);
        $result3 = $this->ThongKe->query($sql3);
        $this->set('kq3s', $result3);
        $this->set('NGAY', $NGAY);
		$this->set('NGAYKT', $NGAYKT);
    }

    

    function viewcongno (){
        $sql1 = 'create or replace view conthieu as
                select b.id, coalesce(sum(a.tongtienhd), 0) - coalesce(sum(a.tiendatra), 0) as conthieu
                from hoadonban a right join khachhang b on a.makh = b.id
                where (a.ngayban is null) or (a.ngayban <= now())
                group by b.id';
        $sql2 = 'create or replace view datra as
                select b.id, coalesce(sum(a.sotienthanhtoan), 0) as datra
                from thanhtoan a right join khachhang b on a.makh = b.id
                where (a.ngaythanhtoan is null) or (a.ngaythanhtoan <= now())
                group by b.id';
        $sql3 = 'select b.id, b.tenkh ,a.conthieu - c.datra as congno
                from conthieu a join khachhang b on a.id = b.id
                    join datra c on b.id = c.id
                group by b.id';
        $result1 = $this->ThongKe->query($sql1);
        $this->set('kq1s', $result1);
        $result2 = $this->ThongKe->query($sql2);
        $this->set('kq2s', $result2);
        $result3 = $this->ThongKe->query($sql3);
        $this->set('kq3s', $result3);
    }

    function viewluong($THANG= null){
        //$this->layout = 'thongke';
        //$title = "Xem lương nhân viên";
        /*$sql = 'select a.MANV, count(a.MACLV) as SOCA, (count(a.MACLV)*b.LUONG) as TIENLUONG
                from chamcong a, calamviec b
                where (month(a.NGAYLAM) = ' . '"' . $THANG  . '")
                group by a.MANV';*/
				
		$sql = 'select MANV, TENNV, NGAYLAM, (sum(round((TGKT-TGBD)/10000,0))* LUONG) as LUONG
				from chamcong, calamviec, nhanvien
				where (month(NGAYLAM) = ' . '"' . $THANG  . '")
				group by MANV';		
        $result = $this->ThongKe->query($sql);
        $this->set('kqs', $result);
        $this->set('LUONG');
        $this->set('THANG', $THANG);
    }

	function view4(){
		$sql = 'select MANV, TENNV, NGAYLAM, (sum(round((TGKT-TGBD)/10000,0))* LUONG) as LUONG
				from chamcong, calamviec, nhanvien
				group by MANV';
        $result = $this->ThongKe->query($sql);
        $this->set('kqs', $result);
    }





    function congnos($NGAY = null, $NGAYKT=null){ //congnongay
    //$this->layout = 'thongke';
		$sqla = 'select a.MAKH, b.TENKH, a.TONGTIENHD from hoadonban a, khachhang b where NGAYBAN >= ' . '"' . $NGAY . '"' . ' and NGAYBAN <= ' . '"' . $NGAYKT . '" and a.MAKH=b.id';
        $resulta = $this->ThongKe->query($sqla);
        $this->set('kqas', $resulta);
		
		$sqla1 = 'select a.MAKH, b.TENKH, a.TIENDATRA from hoadonban a, khachhang b where NGAYBAN >= ' . '"' . $NGAY . '"' . ' and NGAYBAN <= ' . '"' . $NGAYKT . '" and a.MAKH=b.id';
        $resulta1 = $this->ThongKe->query($sqla1);
        $this->set('kqa1s', $resulta1);

        $sqlb = 'select MAKH, TENKH, SOTIENTHANHTOAN from thanhtoan a, khachhang b where NGAYTHANHTOAN >= ' . '"' . $NGAY . '"' . ' and NGAYTHANHTOAN <= ' . '"' . $NGAYKT . '" and a.MAKH=b.id';
        $resultb = $this->ThongKe->query($sqlb);
        $this->set('kqbs', $resultb);

        $sqlc = 'select sum(TONGTIENHD-TIENDATRA) as TONGNO from hoadonban where NGAYBAN >= ' . '"' . $NGAY . '"' . ' and NGAYBAN <= ' . '"' . $NGAYKT . '"';
        $resultc = $this->ThongKe->query($sqlc);
        $this->set('kqcs', $resultc);

        $sqld = 'select sum(SOTIENTHANHTOAN) as TONGTHANHTOAN from thanhtoan where NGAYTHANHTOAN >= ' . '"' . $NGAY . '"' . ' and NGAYTHANHTOAN <= ' . '"' . $NGAYKT . '"';
        $resultd = $this->ThongKe->query($sqld);
        $this->set('kqds', $resultd);

        $this->set('NGAY', $NGAY);
		$this->set('NGAYKT', $NGAYKT);
    }

		

    
    public function tonkho() {}
    public function chamcong() {}
    public function congno(){}
    public function luong(){}
    
}